from generate_common import handleCustomApply

def genItemNumRatio(ruleDatum, ruleAct, filterDatum, hsp_abbr):

    # 从filterDatum获得 than 默认是 absolute
    than = filterDatum.get('than', 'absolute')
    if than != 'absolute':
        raise ValueError("Unsupported 'than' value: {}".format(than))

    # 从filterDatum获得 value
    value = filterDatum['value']

    # 从filterDatum获得 operator
    operator = filterDatum['operator']
    operSql = ""
    if operator == 'gt':
        operSql = ">"
    elif operator == 'lt':
        operSql = "<"
    elif operator == 'eq':
        operSql = "="
    elif operator == 'gte':
        operSql = ">="
    elif operator == 'lte':
        operSql = "<="

    # 从filterDatum获得 numerator
    numeratorObj = filterDatum['numerator']
    numeratorBy = numeratorObj.get('by', 'sum')
    numeratorObjApply = numeratorObj['value_apply']
    numeratorNames = numeratorObj['value']

    # 从filterDatum获得 denominator
    denominatorObj = filterDatum['denominator']
    denominatorBy = denominatorObj.get('by', 'sum')
    denominatorObjApply = denominatorObj['value_apply']
    denominatorNames = denominatorObj['value']

    # 处理 numerator 的 custom_apply
    numeratorCodesSql, numeratorNamesSql = handleCustomApply(numeratorObjApply, numeratorNames, type='item', tblAbbr='sie')
    # 处理 denominator 的 custom_apply
    denominatorCodesSql, denominatorNamesSql = handleCustomApply(denominatorObjApply, denominatorNames, type='item', tblAbbr='sie')

    """
    ============ 思路 ============
    在这个流程当中，将会用到 vb 与 vb2，分别用来计算分子分母的数量
    1. 从 sie， 获得分子列表，计算数量，区分 hsp_abbr, j_isin, j_year，统计数量，插入到 vb 表中，放在 num1
    2. 从 sie， 获得分母列表，计算数量，区分 hsp_abbr, j_isin, j_year，统计数量，插入到 vb2 表中，放在 num1
    3. va 表中删除不符合比率的数据，比率由 vb 与 vb2 关联后两个 num1 计算获得

    ============ 参数 ============
    ● operator：大于，小于，等于
    ● value：数字
    """
    
    sqlList = [
        # 清空临时表
        "TRUNCATE TABLE vlt_tmp_b",
        "TRUNCATE TABLE vlt_tmp_b2",
        # step1: 计算分子
        f"""
            -- item num ratio，计算分子
            INSERT INTO vlt_tmp_b vb (j_isin, j_year, num1)
            SELECT sie.j_isin, sie.j_year, {numeratorBy}(sie.sum_q) AS num1
            FROM scene_item_ext sie
            WHERE sie.hsp_abbr = '{hsp_abbr}'
              {numeratorCodesSql}
            GROUP BY sie.j_isin, sie.j_year
        """,
        # step2: 计算分母
        f"""
            -- item num ratio，计算分母
            INSERT INTO vlt_tmp_b2 vb2 (j_isin, j_year, num1)
            SELECT sie.j_isin, sie.j_year, {denominatorBy}(sie.sum_q) AS num1
            FROM scene_item_ext sie
            WHERE sie.hsp_abbr = '{hsp_abbr}'
              {denominatorCodesSql}
            GROUP BY sie.j_isin, sie.j_year
        """,
        # step3: 删除不符合比率的数据
        f"""
            -- item num ratio，删除不符合比率的数据
            DELETE FROM vlt_tmp_a va
            WHERE NOT EXISTS (
                SELECT 1
                FROM vlt_tmp_b vb
                JOIN vlt_tmp_b2 vb2 ON vb.j_isin = vb2.j_isin AND vb.j_year = vb2.j_year
                WHERE va.j_isin = vb.j_isin
                  AND va.j_year = vb.j_year
                  AND (CAST(vb.num1 AS FLOAT) / NULLIF(vb2.num1, 0)) {operSql} {value}
            )
        """
    ]

    return sqlList
